package com.dldata.devtools30.utils.dbtable.imp;


import com.dldata.devtools30.entity.DevtModelMetadataFieldEntity;
import com.dldata.devtools30.entity.DevtModelMetadataTableEntity;
import com.dldata.devtools30.repository.common.DlBaseJdbcTemplate;
import com.dldata.devtools30.utils.StringUtil;
import com.dldata.devtools30.utils.dbtable.DbTableManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.text.MessageFormat;
import java.util.*;


/**
 * SQLServer的表工具类
 * @author jueyue
 *
 */
public class DbTableServiceMysqlImpl extends DbTableServiceIAbstract {

	private Logger logger = LoggerFactory.getLogger(this.getClass());

	private DlBaseJdbcTemplate dlBaseJdbcTemplate;

	public DbTableServiceMysqlImpl(DlBaseJdbcTemplate dlBaseJdbcTemplate){
		super(dlBaseJdbcTemplate,null);
		this.dlBaseJdbcTemplate = dlBaseJdbcTemplate;
	}

	public void createNewTable(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities, boolean isdrop) {
		if (isdrop){
			StringBuilder strList = new StringBuilder();
			strList.append(" drop table if exists " + table.getCode()+"; ");
			logger.debug(strList.toString());
			dlBaseJdbcTemplate.update(strList.toString());
		}
		StringBuilder strList = new StringBuilder();
		StringBuilder keyList = new StringBuilder();
		strList.append(" Create Table " + table.getCode());
		strList.append(" (");
		String tmps="";
		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			tmps = getFieldLx_MsSql(sysCrfFormFieldEntity, "new");
			if (sysCrfFormFieldEntity.getPkey()!=null&&sysCrfFormFieldEntity.getPkey()==1){
				strList.append(tmps + " not null ,");
				keyList.append(sysCrfFormFieldEntity.getFieldCode() + ",");
			}
			else{
				strList.append(tmps + " null,");
			}
		}
		if (keyList.length() > 0){
			keyList=keyList.deleteCharAt(keyList.length() - 1);
			String msg1 = "constraint PK_{0} primary key ({1}) ";
			msg1= MessageFormat.format(msg1,new Object[]{table.getCode(),keyList.toString()});
			strList.append(msg1);
		}
		strList.append(") ");
		logger.debug(strList.toString());
		dlBaseJdbcTemplate.update(strList.toString());
		return;
	}

	public Boolean isExistTable(String tableName) {

		return getTableInfo(tableName).keySet().size()>0;
	}

	public Integer queryRecordCount(String tableName) {
		DevtModelMetadataTableEntity crfFormEntity=new DevtModelMetadataTableEntity();
		crfFormEntity.setCode(tableName);
		DevtModelMetadataFieldEntity sysCrfFormFieldEntity=
				queryTableFields(crfFormEntity).stream().sorted(
						new Comparator<DevtModelMetadataFieldEntity>() {
							@Override
							public int compare(DevtModelMetadataFieldEntity o1, DevtModelMetadataFieldEntity o2) {
								if(o1.getPkey()==1){
									Integer intnum1=0,intnum2=0;
									intnum1=-1;
									return intnum1.compareTo(intnum2);
								}
								if(o2.getPkey()==1){
									Integer intnum1=0,intnum2=0;
									intnum2=-1;
									return intnum1.compareTo(intnum2);
								}
								return DbTableManager.FIELDTYPE.valueOfCode(o1.getFieldType()).sort.compareTo(DbTableManager.FIELDTYPE.valueOfCode(o2.getFieldType()).sort);
							}
						}
				).findFirst().orElse(null);
		String fieldcode=sysCrfFormFieldEntity==null?"*":sysCrfFormFieldEntity.getFieldCode();
		StringBuilder querySql= new StringBuilder();
		querySql.append(" select count("+fieldcode+")  as total ");
		querySql.append(" from  ");
		querySql.append(tableName);
		List<Map<String, Object>> mapList= dlBaseJdbcTemplate.queryForList(querySql.toString());
		if(mapList.size()>0&&mapList.get(0).get("total")!=null){
			return Integer.parseInt(mapList.get(0).get("total").toString());
		}
		return 0;
	}

	public void updateTable(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
		HashMap<String,HashMap> tableInfo =	getTableInfo(table.getCode());
		LinkedList<String> linkedList=new LinkedList();
		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			String sql=this.updateTableFiled(sysCrfFormFieldEntity,table.getCode(),tableInfo.get(sysCrfFormFieldEntity.getFieldCode().toUpperCase()));
			if(!StringUtil.isEmptyOrLength0(sql)){
				linkedList.add(sql);
			}
		}
		String[] sqlAry=new String[linkedList.size()];
		sqlAry=linkedList.toArray(sqlAry);
		if(sqlAry.length>0){
			dlBaseJdbcTemplate.batchUpdate(sqlAry);
		}
	}

	private String updateTableFiled(DevtModelMetadataFieldEntity fi, String t_code, HashMap coloumnInfo)
	{

		if(coloumnInfo==null){
			StringBuilder strList = new StringBuilder();
			strList.append(" Alter Table " + t_code + " Add " + getFieldLx_MsSql(fi, "new") + " null ");
			return strList.toString();
		}

		String tmps = getFieldLx_MsSql(fi, "update");
		if(coloumnInfo.get("dbType")==null||!tmps.equalsIgnoreCase(coloumnInfo.get("dbType").toString())){
			StringBuilder strList = new StringBuilder();
			strList.append(" Alter Table " + t_code + " modify " + getFieldLx_MsSql(fi, "new") + " ");
			return strList.toString();
		}

		//只有nvarchar和小数数字才进来看长度
		if (tmps .equals("nvarchar")||tmps .equals("varchar")||tmps .equals("varchar2") ){
			if(fi.getTypeLength()!=coloumnInfo.get("columnSize")){
				StringBuilder strList = new StringBuilder();
				strList.append(" Alter Table " + t_code + " modify " + getFieldLx_MsSql(fi, "new") + " ");
				return strList.toString();
			}
		}

		if (tmps.equals("numeric")){
			if(fi.getTypeZeroLength()!=coloumnInfo.get("decimalDigits")){
				StringBuilder strList = new StringBuilder();
				strList.append(" Alter Table " + t_code + " modify " + getFieldLx_MsSql(fi, "new") + " ");
				return strList.toString();
			}
		}

		return new StringBuilder().toString();
	}

	private String getFieldLx_MsSql(DevtModelMetadataFieldEntity sysCrfFormFieldEntity, String strlx)
	{
		String s = "";
		switch (DbTableManager.FIELDTYPE.valueOfCode(sysCrfFormFieldEntity.getFieldType()))
		{
			case BOOLEAN:
				if (strlx .equals("new") )
					s = messageFormat(" {0} bit ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "bit";
				break;
			case DATETIME:
			case DATE:
				if (strlx .equals("new"))
					s = messageFormat(" {0} datetime ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "datetime";
				break;
			case INTEGER:
				if (strlx .equals("new"))
					s = messageFormat(" {0} int ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "int";
				break;
			case CASH:
			case DECEMEMOL:
				if (strlx .equals("new")){
					s = messageFormat(" {0} decimal({1},{2}) ", sysCrfFormFieldEntity.getFieldCode() , String.valueOf(sysCrfFormFieldEntity.getTypeLength()) , String.valueOf(sysCrfFormFieldEntity.getTypeZeroLength()));
				}else{
					s = "numeric";
				}
				break;
			case STRING:
				if (strlx .equals("new")){
					if (sysCrfFormFieldEntity.getTypeLength() >= 3096)
						s = messageFormat(" {0} longtext ", sysCrfFormFieldEntity.getFieldCode());
					else
						s = messageFormat(" {0} varchar({1}) ", sysCrfFormFieldEntity.getFieldCode(), String.valueOf(sysCrfFormFieldEntity.getTypeLength()));
				}else
					s = "varchar";
				break;
		}
		return s;
	}

	private String messageFormat(String msg1, Object... args){
		msg1= MessageFormat.format(msg1,args);
		return msg1;
	}

	/**
	 * 根据数据库的连接参数，获取指定表的基本信息：字段名、字段类型、字段注释
	 * @param table 表名
	 * @return Map集合
	 */
	private HashMap<String,HashMap> getTableInfo(String table){

		HashMap<String,HashMap> resultMap = new HashMap<String,HashMap>();
		Connection conn = null;
		DatabaseMetaData dbmd = null;
		DataSource dataSource =null;
		try {
			dataSource = dlBaseJdbcTemplate.getDataSource();
			conn = DataSourceUtils.getConnection(dataSource);
			dbmd = conn.getMetaData();
			ResultSet resultSet = dbmd.getTables(conn.getCatalog(), conn.getSchema(), table, new String[] { "TABLE" });

			while (resultSet.next()) {
				String tableName=resultSet.getString("TABLE_NAME");
				if(tableName.equalsIgnoreCase(table)){
					ResultSet rs = conn.getMetaData().getColumns(conn.getCatalog(), conn.getSchema(),tableName.toUpperCase(), "%");


					while(rs.next()){
						HashMap map = new HashMap();
						//System.out.println("字段名："+rs.getString("COLUMN_NAME")+"--字段注释："+rs.getString("REMARKS")+"--字段数据类型："+rs.getString("TYPE_NAME"));
						String colName = rs.getString("COLUMN_NAME");
						map.put("code", colName);

						String remarks = rs.getString("REMARKS");
						if(remarks == null){
							remarks = "";
						}
						map.put("remarks",remarks);

						String dbType = rs.getString("TYPE_NAME");
						map.put("dbType",dbType);

						map.put("valueType", changeDbType(dbType));

						int columnSize = rs.getInt("COLUMN_SIZE");  //列大小
						int decimalDigits = rs.getInt("DECIMAL_DIGITS");  //小数位数
						map.put("columnSize",columnSize);
						map.put("decimalDigits",decimalDigits);

						resultMap.put(map.get("code").toString().toUpperCase(),map);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataSourceUtils.releaseConnection(conn,dataSource);
		}

		return resultMap;
	}

	private static String changeDbType(String dbType) {
		dbType = dbType.toUpperCase();
		switch(dbType){
			case "VARCHAR":
			case "VARCHAR2":
			case "CHAR":
				return "1";
			case "NUMBER":
			case "DECIMAL":
				return "4";
			case "INT":
			case "SMALLINT":
			case "INTEGER":
				return "2";
			case "BIGINT":
				return "6";
			case "DATETIME":
			case "TIMESTAMP":
			case "DATE":
				return "7";
			default:
				return "1";
		}
	}



	@Override
	public String createTableSql(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
		StringBuilder strList = new StringBuilder();
		StringBuilder keyList = new StringBuilder();
		strList.append(" Create Table " + table.getCode());
		strList.append(" (");
		strList.append("\n");
		String tmps="";
		for (int i = 0; i < formFieldEntities.size(); i++) {
			strList.append("   ");
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			tmps = getFieldLx_MsSql(sysCrfFormFieldEntity, "new");
			if (sysCrfFormFieldEntity.getPkey()!=null&&sysCrfFormFieldEntity.getPkey()==1){
				strList.append(tmps + " not null ,");
				keyList.append(sysCrfFormFieldEntity.getFieldCode() + ",");
			}
			else{
				strList.append(tmps + " null,");
			}
			strList.append("\n");
		}
		if (keyList.length() > 0){
			keyList=keyList.deleteCharAt(keyList.length() - 1);
			strList.append("   ");
			String msg1 = "constraint PK_{0} primary key ({1}) ";
			msg1= MessageFormat.format(msg1,new Object[]{table.getCode(),keyList.toString()});
			strList.append(msg1);
			strList.append("\n");
		}
		strList.append(") ");
		logger.debug(strList.toString());
		return strList.toString();
	}

	@Override
	public String updateTableSql(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
		StringBuilder strList=new StringBuilder();
		HashMap<String,HashMap> tableInfo =	getTableInfo(table.getCode());
		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			String sql=this.updateTableFiled(sysCrfFormFieldEntity,table.getCode(),tableInfo.get(sysCrfFormFieldEntity.getFieldCode().toUpperCase()));
			logger.debug(sysCrfFormFieldEntity.getFieldCode()+" :sql: "+sql);
			if(!StringUtil.isEmptyOrLength0(sql)){
				strList.append(sql);
				strList.append(";\r\n");
				strList.append("\r\n");
			}
		}
		return strList.toString();
	}

}
